﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
/// Summary description for clsConnection
/// </summary>
public class clsConnection
{
    private const string _strConn = "Data Source=TrungChinh-PC\\SQL;Initial Catalog=CongTT_NB;Integrated Security=True";
    protected SqlConnection _sqlConn;

    public clsConnection()
    {
        //connectDatabase();
    }
    protected Boolean connectDatabase()
    {
        _sqlConn = new SqlConnection();
        try
        {
            _sqlConn.ConnectionString = _strConn;
            if (_sqlConn.State != System.Data.ConnectionState.Closed)
                _sqlConn.Close();
            _sqlConn.Open();
            return true;
        }
        catch
        {
            _sqlConn.Dispose(); //Giai phong vung nho cua lop
            return false;
        }
    }
    public void free() //Giai phong vung nho
    {
        try
        {
            _sqlConn.Close();
            _sqlConn.Dispose(); //Giai phong vung nho cua lop
        }
        catch { }
    }
    public Boolean runSQL(String sql)
    {

        try
        {
            if (!connectDatabase()) return false;
            SqlCommand sqlcomm = new SqlCommand();
            sqlcomm.CommandType = CommandType.Text;
            sqlcomm.Connection = _sqlConn;
            sqlcomm.CommandText = sql;
            sqlcomm.ExecuteNonQuery();
            free();
            return true;
        }
        catch
        {
            return false;
        }
    }
    public Boolean runSQL(string sql, out int kq)
    {
        kq = 0;
        try
        {
            if (!connectDatabase()) return false;
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = _sqlConn;
            sqlComm.CommandType = System.Data.CommandType.Text;
            sqlComm.CommandText = sql;  //Insert, Update, Delete
            kq = sqlComm.ExecuteNonQuery();
            free();
            return (kq != 0);
            //return (sqlComm.ExecuteNonQuery() != 0);
        }
        catch
        {
            kq = 0;
            return false;
        }
    }
    // kiem tra trung ma ko
    public Boolean ktrunSQL(string sql)
    {
        //kq = 0;
        try
        {
            if (!connectDatabase()) return false;

            SqlDataAdapter adap;
            DataTable dt = new DataTable();
            SqlConnection kn = (_sqlConn);
            adap = new SqlDataAdapter(sql, kn);
            adap.Fill(dt);
            free();
            if (dt.Rows.Count == 0)
                return true;
            return false;
            
        }
        catch
        {
            //kq = 0;
            return false;
        }
    }


    public Single runReturnSQL(string sql)
    {
        try
        {
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = _sqlConn;
            sqlComm.CommandType = System.Data.CommandType.Text;
            sqlComm.CommandText = sql;  //Insert, Update, Delete
            return (Convert.ToSingle(sqlComm.ExecuteScalar()));
        }
        catch
        {
            return 0;
        }
    }
    protected Boolean runReturnSQL(string sql, out string kq)
    {
        kq = "";
        try
        {
            if (!connectDatabase()) return false;
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = _sqlConn;
            sqlComm.CommandType = System.Data.CommandType.Text;
            sqlComm.CommandText = sql;  //avg, sum, count,...
            kq = sqlComm.ExecuteScalar().ToString();
            free();
            return (kq != "");
        }
        catch
        {
            kq = "";
            return false;
        }
    }
    public Boolean runSQLValue(string sql, out object kq)
    {
        kq = null;
        try
        {
            if (!connectDatabase()) return false;
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.CommandType = CommandType.Text;
            sqlComm.Connection = _sqlConn;
            sqlComm.CommandText = sql;
            kq = sqlComm.ExecuteScalar();
            free();
            return (kq != null);
        }
        catch
        {
            return false;
        }
    }
    public SqlDataReader exportDataToDatareader(string cols, string table, string cri)
    {
        try
        {
            if (!connectDatabase()) return null;
            string sql = "select " + cols + " from " + table + ((cri.Length == 0) ? "" : " where " + cri).ToString();
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = _sqlConn;
            sqlComm.CommandType = System.Data.CommandType.Text;
            sqlComm.CommandText = sql;  //Insert, Update, Delete
            //free(); 
            return (sqlComm.ExecuteReader());
        }
        catch
        {
            return null;
        }
    }
    public DataTable exportDataToDataTable(string cols, string table, string cri)
    {
        try
        {
            if (!connectDatabase()) return null;
            string sql = "select " + cols + " from " + table + ((cri.Length == 0) ? "" : " where " + cri).ToString();
            SqlDataAdapter sqlDaad = new SqlDataAdapter(sql, _sqlConn);
            DataTable tbl = new DataTable();
            sqlDaad.Fill(tbl);
            free();
            return (tbl);
        }
        catch
        {
            return null;
        }
    }
}